﻿CREATE PROCEDURE [dbo].[spMomPortJournal]
	@OwnerID int
AS
set nocount on
	declare @message nvarchar(1000)
	set @message = 'MOMPortJournal' + '  ' + convert( varchar, getdate(), 9 )
	RAISERROR (@message, 0, 1) WITH NOWAIT


declare @ProductRevenueAccountID int
declare @TaxRevenueAccountID int
declare @ShippingRevenueAccountID int
declare @MiscExpenseAccountID int
declare @OrderID int
declare @Amount money

select @ProductRevenueAccountID = ProductRevenueAccountID, 
	@TaxRevenueAccountID = TaxRevenueAccountID,
	@ShippingRevenueAccountID = ShippingRevenueAccountID,
	@MiscExpenseAccountID = MiscExpenseAccountID
from tblOwner
where OwnerID = @OwnerID

insert into tblAccount ( CreateDate, CardNumber, CardExpiration, CurrencyID, AddressID, PersonID, AccountTypeCode )
select min( T1.odr_date ) as odr_date, T1.cardnum, max(T1.exp) as exp, 1 as currencyid, 
	max(T5.AddressID) as AddressID, max( T4.PersonID ) as PersonID, 'R' as AccountTypeCode
from openquery( MOMPort, 'select order, odr_date, cardnum, exp, cardholder from cms where cardnum is not null' ) as T1
	join tblOrderSourceID T2 on cast(T1.[order] as varchar(20)) = T2.IDvalue
		and T2.IDSourceCode = 'E'
		and T2.OwnerID = @OwnerID
	join tblOrderHeader T3 on T2.OrderID = T3.OrderID
	join tblCustomer T4 on T3.CustomerID = T4.CustomerID
	join tblPerson T5 on T4.PersonID = T5.PersonID
group by T1.cardnum


update T3 set AccountID = T4.AccountID
from openquery( MOMPort, 'select order, odr_date, cardnum, exp, cardholder from cms where cardnum is not null' ) as T1
	join tblOrderSourceID T2 on cast(T1.[order] as varchar(20)) = T2.IDvalue
		and T2.IDSourceCode = 'E'
		and T2.OwnerID = @OwnerID
	join tblOrderHeader T3 on T2.OrderID = T3.OrderID
	join tblAccount T4 on T1.cardnum = T4.cardnumber

declare cCharges cursor for 
select T2.OrderID, T1.odr_date, T1.Amount, T1.merch, T1.shipping, T1.tax, T3.AccountID
from openquery( MOMPort, 'select T1.order, T1.odr_date, T1.amount, T2.merch, T2.shipping, T2.tax
		from journal T1 left outer join 
			( select order, inpart, amount, max(merch) as merch, max(shipping) as shipping, max(itax + stax + ntax) as tax
				from invoice 
				group by order, inpart, amount ) as T2 on T1.order = T2.order
			and T1.inpart = T2.inpart
			and T1.Amount = T2.Amount
		where T1.acct in ( ''PY'', ''PI'')') as T1
	join tblOrderSourceID T2 on cast(T1.[order] as varchar(20)) = T2.IDvalue
		and T2.IDSourceCode = 'E'
		and T2.OwnerID = @OwnerID
	join tblOrderHeader T3 on T2.OrderID = T3.OrderID
where T1.Amount is not null
open cCharges
declare @ProductTotal money
declare @Shipping money
declare @Tax money
declare @CreateDate smalldatetime
declare @AccountID int
declare @inpart char(1)
fetch next from cCharges into @OrderID, @CreateDate, @Amount, @ProductTotal, @Shipping, @Tax, @AccountID
while @@fetch_status = 0
begin
	insert into tblTransaction( 
		TransactionTypeCode, 
		TransactionStatusCode, 
		Amount,
		CreateDate,
		ResponseDate,
		OrderID
		)
	select 'B', 'P', @Amount, @CreateDate, @CreateDate, @OrderID

	declare @TransactionID int
	set @TransactionID = SCOPE_IDENTITY()

	insert into tblTransactionDetail( TransactionID, TransactionDetailTypeCode, AccountID, Amount )
	select @TransactionID, 'C', @AccountID, @Amount

	insert into tblTransactionDetail( TransactionID, TransactionDetailTypeCode, AccountID, Amount )
	select @TransactionID, 'D', ProductRevenueAccountID, @ProductTotal
	from tblOwner 
	where OwnerID = @OwnerID
		and coalesce(@ProductTotal, 0) <> 0

	insert into tblTransactionDetail( TransactionID, TransactionDetailTypeCode, AccountID, Amount )
	select @TransactionID, 'D', ShippingRevenueAccountID, @Shipping
	from tblOwner 
	where OwnerID = @OwnerID
		and coalesce(@Shipping, 0) <> 0

	insert into tblTransactionDetail( TransactionID, TransactionDetailTypeCode, AccountID, Amount )
	select @TransactionID, 'D', TaxRevenueAccountID, @Tax
	from tblOwner 
	where OwnerID = @OwnerID
		and coalesce(@Tax, 0) <> 0

	fetch next from cCharges into @OrderID, @CreateDate, @Amount, @ProductTotal, @Shipping, @Tax, @AccountID
end
close cCharges
deallocate cCharges

StartRefunds:

declare @ProductRefund money
declare @TaxRefund money
declare @ShippingRefund money
declare cRefunds cursor for 
select T2.OrderID, T1.odr_date, 0 - T1.Amount
from openquery( MOMPort, 'select T1.order, T1.odr_date, T1.amount
		from journal T1 
		where T1.acct in ( ''RF'')') as T1
	join tblOrderSourceID T2 on cast(T1.[order] as varchar(20)) = T2.IDvalue
		and T2.IDSourceCode = 'E'
		and T2.OwnerID = @OwnerID
	join tblOrderHeader T3 on T2.OrderID = T3.OrderID
open cRefunds
fetch next from cRefunds into @OrderID, @CreateDate, @Amount
while @@fetch_status = 0
begin
	-- Create refund records.
	-- Apply the amount first to product and taxes, and then to shipping
	exec spNewRefunds @OrderID, 0

	set @ProductRefund = 0
	set @TaxRefund = 0
	set @ShippingRefund = 0

	declare @RemainingAmount money
	set @RemainingAmount = @Amount
	declare @RefundID int
	declare @MaxCost money
	declare cRefundDetail cursor for
	select RefundID, MaxCost from tblRefund 
		where OrderID = @OrderID and Cost = 0 and MaxCost > 0
		order by OrderDetailID desc, OrderTaxID desc
	open cRefundDetail
	fetch next from cRefundDetail into @RefundID, @MaxCost
	while @@fetch_status = 0 and @RemainingAmount > 0
	begin
		declare @cost money
		if @MaxCost > @RemainingAmount
			set @cost = @RemainingAmount
		else
			set @cost = @MaxCost

		update tblRefund set Cost = @cost where RefundID = @RefundID

		set @RemainingAmount = @RemainingAmount - @cost

		select @ProductRefund = @ProductRefund + @Cost
		from tblRefund
		where RefundID = @RefundID
			and OrderDetailID is not null

		select @TaxRefund = @TaxRefund + @Cost
		from tblRefund
		where RefundID = @RefundID
			and OrderTaxID is not null

		select @ShippingRefund = @ShippingRefund + @Cost
		from tblRefund
		where RefundID = @RefundID
			and OrderTaxID is null and OrderDetailID is null

		fetch next from cRefundDetail into @RefundID, @MaxCost
	end
	close cRefundDetail
	deallocate cRefundDetail

	insert into tblTransaction( 
		TransactionTypeCode, 
		TransactionStatusCode, 
		Amount,
		CreateDate,
		ResponseDate,
		OrderID
		)
	select 'R', 'P', @Amount, @CreateDate, @CreateDate, @OrderID

	set @TransactionID = SCOPE_IDENTITY()

	update tblRefund set TransactionID = @TransactionID
	where OrderID = @OrderID
		and TransactionID is null

	-- Debit the customers account
	insert into tblTransactionDetail( TransactionID, TransactionDetailTypeCode, AccountID, Amount )
	select @TransactionID, 'D', AccountID, @Amount
	from tblOrderHeader where OrderID = @OrderID

	-- Credit ProductRevenue
	if @ProductRefund > 0
		insert into tblTransactionDetail( TransactionID, TransactionDetailTypeCode, AccountID, Amount )
		select @TransactionID, 'C', ProductRevenueAccountID, @ProductRefund
		from tblOwner 
		where OwnerID = @OwnerID
			and @ProductRefund <> 0

	-- Credit ShippingRevenue
	if @ShippingRefund > 0
		insert into tblTransactionDetail( TransactionID, TransactionDetailTypeCode, AccountID, Amount )
		select @TransactionID, 'C', ShippingRevenueAccountID, @ShippingRefund
		from tblOwner 
		where OwnerID = @OwnerID
			and @ShippingRefund <> 0

	-- Credit TaxRevenue
	if @TaxRefund > 0
		insert into tblTransactionDetail( TransactionID, TransactionDetailTypeCode, AccountID, Amount )
		select @TransactionID, 'C', TaxRevenueAccountID, @TaxRefund
		from tblOwner 
		where OwnerID = @OwnerID
			and @TaxRefund <> 0

	-- If there is any remaining unallocated refund amount then apply it to 
	-- Misc Expenses
	if @RemainingAmount > 0
		insert into tblTransactionDetail( TransactionID, TransactionDetailTypeCode, AccountID, Amount )
		select @TransactionID, 'C', MiscExpenseAccountID, @RemainingAmount
		from tblOwner 
		where OwnerID = @OwnerID
			and @RemainingAmount <> 0

	fetch next from cRefunds into @OrderID, @CreateDate, @Amount
end
close cRefunds
deallocate cRefunds

RETURN 0